package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.MakeDTO;


@ManagedBean(name = "makeModelBean")
//@RequestScoped
@ViewScoped
public class MakeModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<MakeDTO> getMakeList() throws SQLException
	{
		ArrayList<MakeDTO> makeList=new ArrayList<MakeDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MAKES Order by MAKE_NAME");  
		ResultSet result =  ps.executeQuery();
		MakeDTO m=null;
				while(result.next())
				{
					m=new MakeDTO();
					m.setMakeId(result.getInt("MAKE_ID"));
					m.setMakeName(result.getString("MAKE_NAME"));
					
					makeList.add(m);
				}
		
		
		return makeList;
	}
	
	public MakeDTO getMake(String makeName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MAKES Where upper(MAKE_NAME)=upper(?)");  
		ps.setString(1, makeName);
		ResultSet result =  ps.executeQuery();
		MakeDTO m=null;
				if(result.next())
				{
					m=new MakeDTO();
					m.setMakeId(result.getInt("MAKE_ID"));
					m.setMakeName(result.getString("MAKE_NAME"));
					
				}
		
		return m;
	}
	
	public MakeDTO getMake(int makeId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_MAKES Where MAKE_ID=?");  
		ps.setInt(1, makeId);
		ResultSet result =  ps.executeQuery();
		MakeDTO m=null;
				if(result.next())
				{
					m=new MakeDTO();
					m.setMakeId(result.getInt("MAKE_ID"));
					m.setMakeName(result.getString("MAKE_NAME"));
					
				}
		
		
		return m;
	}
	
	public boolean addMake(String makeName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_MAKES Values(SQN_MAKE.NEXTVAL,?)");  
		ps.setString(1, makeName);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteMake(int makeId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_MAKES Where MAKE_ID=?");  
		ps.setInt(1, makeId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean editMake(String makeName,int makeId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_MAKES Set MAKE_NAME=? Where MAKE_ID=?");  
		ps.setString(1, makeName);
		ps.setInt(2, makeId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}	
	
	public String[] getMakeList(String userId) throws SQLException
	{
		ArrayList<MakeDTO> makeList=new ArrayList<MakeDTO>();
		String[] makeArrList;
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from RESPONSIBILITIES Where user_id=?");  
		ps.setString(1, userId);
		ResultSet result =  ps.executeQuery();
		MakeDTO m=null;
				while(result.next())
				{
					m=new MakeDTO();
					m.setMakeId(result.getInt("MAKE_ID"));
					makeList.add(m);
				}
				
		makeArrList=new String[makeList.size()];
		
		for(int i=0;i<makeList.size();i++)
		{
			makeArrList[i]=String.valueOf(makeList.get(i).getMakeId());
		}
		
		return makeArrList;
	}

}